/*
	SELECT * FROM FN_LOAD_ALL_WIP(1,NULL,NULL)
	@HOLD_TYPE = 1 - ALL
		   = 2 - WITHOUT HOLD
		   = 3 - WITH HOLD

	@BASE_PRODUCT_IDS = NULL OR '' - ALL BASE PRODUCTS WIP
			  = '2,3,4,6,7' -WIP FOR THE GIVEN BASE PRODUCTS
	
	@FG_PROCESSSTEP_IDS = NULL OR '' - ALL PRODUCTS
			    = '10,1091' -WIP FOR THE GIVEN FG PRODUCTS AND THEIR FLOWS

	IF @BASE_PRODUCT_IDS IS PRESENT THEN RETURNS THE WIP FOR THE GIVEN BASE PRODUCTS.
	IF @BASE_PRODUCT_IDS NOT PRESENT AND @FG_PROCESSSTEP_IDS IS PRESENT THEN RETURNS WIP FOR THE GIVEN FG PROCESS FLOWS
	IF @BASE_PRODUCT_IDS AND @FG_PROCESSSTEP_IDS NOT PRESENT THEN RETURNS WIP FOR ALL THE PRODUCTS
*/
ALTER FUNCTION FN_LOAD_ALL_WIP ( @HOLD_TYPE INT, @BASE_PRODUCT_IDS VARCHAR(800), @FG_PROCESSSTEP_IDS VARCHAR(800) )
RETURNS @FN_RESULTS TABLE ( PART_NUMBER VARCHAR(50), VENDOR_NAME VARCHAR(50), LOT_NUMBER VARCHAR(50), QTY_IN INT, DATE_IN DATETIME,
		 IS_REGULAR_INTRANSIT INT, PS_TYPE VARCHAR(50), PS_TYPE_NAME VARCHAR(50), PROCESSSTEP_ID INT)
AS
BEGIN
/*
		COLUMN 'IS_REGULAR_INTRANSIT' FLAG HAS THE FOLLOWING VALUES
		
		0 = INDICATES THE QTY IS NOT INTRANSIT ( REGULAR )
		1 = INDICATES INTRANSIT QTY
*/
IF ( @BASE_PRODUCT_IDS IS NOT NULL AND LEN(LTRIM(RTRIM(@BASE_PRODUCT_IDS))) > 0 ) --TO GET WIP ONLY FOR THE GIVEN BASE PRODUCTS
 BEGIN
	DECLARE @BASE_PRODUCTS_TABLE TABLE( BASE_PRODUCT_ID INT )
	INSERT INTO @BASE_PRODUCTS_TABLE SELECT PROCESSSTEP_ID FROM fn_get_process_steps_in_string(@BASE_PRODUCT_IDS)
	IF ( @HOLD_TYPE = 2 ) --WITHOUT HOLD
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME, LT.LOT_NUMBER, 
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN LT.WAFER_QTY_IN ELSE LT.QTY_IN END ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Running' AND LOT_MANUAL_HOLD = 1 )
					AND P.P_PARENT_PRODUCT_ID IN (SELECT BASE_PRODUCT_ID FROM @BASE_PRODUCTS_TABLE)

		UNION ALL --TRANSIT LOTS
		 SELECT P.PART_NUMBER, L.L_VENDOR_NAME,
			LT.LOT_NUMBER, SL.SHIP_QTY AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
			FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
			WHERE LT.LOT_ID = SL.LOT_ID
				AND L.LOCATION_ID = LT.LOCATION_ID		
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND SL.STATUS = 'TRANSIT'
				AND P.P_PARENT_PRODUCT_ID IN (SELECT BASE_PRODUCT_ID FROM @BASE_PRODUCTS_TABLE)
		
		ORDER BY PART_NUMBER, L_VENDOR_NAME, LOT_NUMBER
	 END
	ELSE IF ( @HOLD_TYPE = 3 ) --WITH HOLD
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME, LT.LOT_NUMBER, 
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN LT.WAFER_QTY_IN ELSE LT.QTY_IN END ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Hold' OR LOT_MANUAL_HOLD = 0 )
					AND P.P_PARENT_PRODUCT_ID IN (SELECT BASE_PRODUCT_ID FROM @BASE_PRODUCTS_TABLE)

		UNION ALL --TRANSIT LOTS
		 SELECT P.PART_NUMBER, L.L_VENDOR_NAME,
			LT.LOT_NUMBER, SL.SHIP_QTY AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT,  PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
			FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
			WHERE LT.LOT_ID = SL.LOT_ID
				AND L.LOCATION_ID = LT.LOCATION_ID
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND SL.STATUS = 'TRANSIT'
				AND P.P_PARENT_PRODUCT_ID IN (SELECT BASE_PRODUCT_ID FROM @BASE_PRODUCTS_TABLE)

		ORDER BY PART_NUMBER, L_VENDOR_NAME, LOT_NUMBER	
	 END
	ELSE --ALL
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME, LT.LOT_NUMBER, 
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN LT.WAFER_QTY_IN ELSE LT.QTY_IN END ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Running' OR LOT_CURRENT_STATUS = 'Hold' )
					AND P.P_PARENT_PRODUCT_ID IN (SELECT BASE_PRODUCT_ID FROM @BASE_PRODUCTS_TABLE)
		UNION ALL --TRANSIT LOTS
		 SELECT P.PART_NUMBER, L_VENDOR_NAME,
			LT.LOT_NUMBER, SL.SHIP_QTY AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT,  PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
			FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
			WHERE LT.LOT_ID = SL.LOT_ID
				AND L.LOCATION_ID = LT.LOCATION_ID		
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND SL.STATUS = 'TRANSIT'
				AND P.P_PARENT_PRODUCT_ID IN (SELECT BASE_PRODUCT_ID FROM @BASE_PRODUCTS_TABLE)

		ORDER BY PART_NUMBER, L_VENDOR_NAME, LOT_NUMBER
	 END
 END
ELSE IF ( @FG_PROCESSSTEP_IDS IS NOT NULL AND LEN(LTRIM(RTRIM(@FG_PROCESSSTEP_IDS))) > 0 ) --TO GET WIP ONLY FOR THE GIVEN FG PRODUCTS AND THEIR FLOWS
 BEGIN
	DECLARE @FG_PROCESSSTEPS_TABLE TABLE( FG_PROCESSSTEP_ID INT )
	DECLARE @PROCESSSTEPS_TABLE TABLE ( PROCESSSTEP_ID INT )
	DECLARE @FG_PROCESSSTEP_ID INT
	INSERT INTO @FG_PROCESSSTEPS_TABLE SELECT PROCESSSTEP_ID FROM fn_get_process_steps_in_string(@FG_PROCESSSTEP_IDS)
	--LOADING ALL THE PROCESSSTEPS IN EACH FG PROCESS FLOW
	SELECT TOP 1 @FG_PROCESSSTEP_ID = FG_PROCESSSTEP_ID FROM @FG_PROCESSSTEPS_TABLE
	WHILE  ( @FG_PROCESSSTEP_ID > 0 )
	 BEGIN
		INSERT INTO @PROCESSSTEPS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @FG_PROCESSSTEP_ID,0 )
		DELETE FROM @FG_PROCESSSTEPS_TABLE WHERE FG_PROCESSSTEP_ID = @FG_PROCESSSTEP_ID
		SET @FG_PROCESSSTEP_ID = 0
		SELECT TOP 1 @FG_PROCESSSTEP_ID = FG_PROCESSSTEP_ID FROM @FG_PROCESSSTEPS_TABLE
	 END

	IF ( @HOLD_TYPE = 2 ) --WITHOUT HOLD
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME, LT.LOT_NUMBER, 
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN LT.WAFER_QTY_IN ELSE LT.QTY_IN END ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Running' AND LOT_MANUAL_HOLD = 1 )
					AND P.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM @PROCESSSTEPS_TABLE)

		UNION ALL --TRANSIT LOTS
		 SELECT P.PART_NUMBER, L.L_VENDOR_NAME,
			LT.LOT_NUMBER, SL.SHIP_QTY AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
			FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
			WHERE LT.LOT_ID = SL.LOT_ID
				AND L.LOCATION_ID = LT.LOCATION_ID		
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND SL.STATUS = 'TRANSIT'
				AND P.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM @PROCESSSTEPS_TABLE)
		
		ORDER BY PART_NUMBER, L_VENDOR_NAME, LOT_NUMBER
	 END
	ELSE IF ( @HOLD_TYPE = 3 ) --WITH HOLD
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME, LT.LOT_NUMBER, 
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN LT.WAFER_QTY_IN ELSE LT.QTY_IN END ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Hold' OR LOT_MANUAL_HOLD = 0 )
					AND P.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM @PROCESSSTEPS_TABLE)

		UNION ALL --TRANSIT LOTS
		 SELECT P.PART_NUMBER, L.L_VENDOR_NAME,
			LT.LOT_NUMBER, SL.SHIP_QTY AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT,  PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
			FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
			WHERE LT.LOT_ID = SL.LOT_ID
				AND L.LOCATION_ID = LT.LOCATION_ID
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND SL.STATUS = 'TRANSIT'
				AND P.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM @PROCESSSTEPS_TABLE)

		ORDER BY PART_NUMBER, L_VENDOR_NAME, LOT_NUMBER	
	 END
	ELSE --ALL
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME, LT.LOT_NUMBER, 
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN LT.WAFER_QTY_IN ELSE LT.QTY_IN END ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Running' OR LOT_CURRENT_STATUS = 'Hold' )
					AND P.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM @PROCESSSTEPS_TABLE)
		UNION ALL --TRANSIT LOTS
		 SELECT P.PART_NUMBER, L_VENDOR_NAME,
			LT.LOT_NUMBER, SL.SHIP_QTY AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT,  PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
			FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
			WHERE LT.LOT_ID = SL.LOT_ID
				AND L.LOCATION_ID = LT.LOCATION_ID		
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND SL.STATUS = 'TRANSIT'
				AND P.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM @PROCESSSTEPS_TABLE)

		ORDER BY PART_NUMBER, L_VENDOR_NAME, LOT_NUMBER
	 END
 END
ELSE -- ALL 
 BEGIN
	IF ( @HOLD_TYPE = 2 ) --WITHOUT HOLD
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME, LT.LOT_NUMBER, 
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN LT.WAFER_QTY_IN ELSE LT.QTY_IN END ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L (NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Running' AND LOT_MANUAL_HOLD = 1 )
			UNION ALL --TRANSIT LOTS
			 SELECT P.PART_NUMBER, L.L_VENDOR_NAME,
				LT.LOT_NUMBER, SL.SHIP_QTY AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.LOT_ID = SL.LOT_ID
					AND L.LOCATION_ID = LT.LOCATION_ID		
					AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND SL.STATUS = 'TRANSIT'					
			
			ORDER BY PART_NUMBER, L_VENDOR_NAME, LOT_NUMBER			
	 END
	ELSE IF ( @HOLD_TYPE = 3 ) --WITH HOLD
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME, LT.LOT_NUMBER, 
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN LT.WAFER_QTY_IN ELSE LT.QTY_IN END ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Hold' OR LOT_MANUAL_HOLD = 0 )

			UNION ALL --TRANSIT LOTS
			 SELECT P.PART_NUMBER, L.L_VENDOR_NAME,
				LT.LOT_NUMBER, SL.SHIP_QTY AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT,  PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.LOT_ID = SL.LOT_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND SL.STATUS = 'TRANSIT'
	
			ORDER BY PART_NUMBER, L_VENDOR_NAME, LOT_NUMBER	
	 END
	ELSE --ALL
	 BEGIN
		INSERT INTO @FN_RESULTS
			SELECT P.PART_NUMBER, L.L_VENDOR_NAME, LT.LOT_NUMBER, 
				(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN LT.WAFER_QTY_IN ELSE LT.QTY_IN END ) AS QTY_IN, 
				LT.DATE_IN, 0 AS IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND (WAFER_QTY_IN > 0 OR QTY_IN > 0)
					AND ( LOT_CURRENT_STATUS = 'Running' OR LOT_CURRENT_STATUS = 'Hold' )

			UNION ALL --TRANSIT LOTS
			 SELECT P.PART_NUMBER, L_VENDOR_NAME,
				LT.LOT_NUMBER, SL.SHIP_QTY AS QTY_IN, SL.SHIP_DATE AS DATE_IN, 0 AS IS_REGULAR_INTRANSIT,  PS_TYPE, PS_TYPE_NAME, LT.PROCESSSTEP_ID
				FROM SHIPPED_LOT SL(NOLOCK), LOT_TABLE LT(NOLOCK), PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK), LOCATION L(NOLOCK)
				WHERE LT.LOT_ID = SL.LOT_ID
					AND L.LOCATION_ID = LT.LOCATION_ID		
					AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND SL.STATUS = 'TRANSIT'
	
			ORDER BY PART_NUMBER, L_VENDOR_NAME, LOT_NUMBER
	 END
 END

RETURN
END